﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Threading;

namespace SQLProxy
{
    class DatabaseConnector
    {
        private static int database;
        private SqlConnection con;
        private SqlDataAdapter resDA, tecDA;
        private const string myDataBase = "g21-A-db";
        private string connectionString;

        public static int Database
        {
            get { return DatabaseConnector.database; }
            set { DatabaseConnector.database = value; }
        }

        public DatabaseConnector()
        {
            this.connectionString = "Server=cmovel.rnl.ist.utl.pt;Initial Catalog=" + myDataBase + database +
                ";User ID=g21-A;Password=g21-A.1847";
            // connect to the database
            con = new SqlConnection();
            con.ConnectionString = this.connectionString;
        }

        public DataSet getResidenceDataSet(ClientData client, DataSet versionVector)
        {                
            // variable indicates if data is up-to-date
            bool upToDate = true;
            // verify if the server write-set is up-to-date with the client
            DataTableReader dtReader = versionVector.CreateDataReader();
            // for each vector check if the server's is up-to-date
            con.Open();
            SqlTransaction myTransaction = con.BeginTransaction();
            SqlCommand myCommand = con.CreateCommand();
            myCommand.Transaction = myTransaction;

            // pass through all data
            try
            {
                // dtReader is the user write-set
                while (dtReader.Read())
                {
                    // check to see if the server is up-to-date
                    myCommand.CommandText = "select * from version_vector where resi_id=" + dtReader.GetInt32(0);
                    SqlDataReader dataReader = myCommand.ExecuteReader();
                    if (dataReader.Read())
                    {
                        // only needs to check the other server
                        if (dataReader.GetInt32(((database == 1) ? 2 : 1)) < 
                            dtReader.GetInt32(((database == 1) ? 2 : 1)))
                        {
                            dtReader.Close();

                            Console.WriteLine("> Dados do servidor nao estao atualizados!");

                            // data not up-to-date, launch new thread to update data
                            string thisConString = this.connectionString;
                            string otherConString = "Server=cmovel.rnl.ist.utl.pt;Initial Catalog="+myDataBase+
                                ((database == 1) ? 2 : 1)+";User ID=g21-A;Password=g21-A.1847";
                            ConsistencyManager cons = new ConsistencyManager(thisConString, otherConString,
                                database, ((database == 1) ? 2 : 1), versionVector);

                            // data is not up-to-date, so cannot send data to client
                            upToDate = false;
                            dataReader.Close();
                            break;
                        }
                    }
                    dataReader.Close();
                }
                if (dtReader.IsClosed == false)
                    dtReader.Close();
                myTransaction.Commit();
            }
            catch (Exception e)
            {
                myTransaction.Rollback();
                return null;
            }
            finally
            {
                con.Close();
                myTransaction.Dispose();
                myCommand.Dispose();
            }

            // if data is up-to-date can send to client
            if (upToDate == true)
            {
                Console.WriteLine("> Dados do servidor estao atualizados!");

                // issue a command to retrieve a dataset containing the residence info
                string sqlString = "select r.resi_id as resi_id," +
                    "r.address as address,r.district as district,r.client as client,r.category as category" +
                    ",v.visit_date as visit_date,r.topology as topology,r.cooling as cooling," +
                    "r.heating as heating,r.isolation as isolation," +
                    "r.coverage as coverage,r.glasses as glasses from technician t,visiting_residence v ,residence r where " +
                    "t.id='" + client.Username + "' and t.password='" + client.Password +
                    "' and day(v.visit_date)=day(getdate()) " +
                    "and v.id=t.id and r.resi_id=v.resi_id";
                resDA = new SqlDataAdapter(sqlString, con);
                DataSet resDS = new DataSet();
                con.Open();
                resDA.Fill(resDS, "residence");
                con.Close();
                return resDS;
            }
            else
            {
                return null;
            }
        }

        private void updateDatabase()
        {
            throw new NotImplementedException();
        }

        public DataSet getTechnicianInfo(ClientData client)
        {
            // issue a command to retrieve a dataset containing the residence info
            con.Open();
            string sqlString = "select id,password,name,district,category,supervisor "+
                "from technician where id='" + client.Username +
                "' and password='" + client.Password + "'";
            tecDA = new SqlDataAdapter(sqlString, con);
            DataSet tecDS = new DataSet();
            tecDA.Fill(tecDS, "technician");
            con.Close();
            return tecDS;
        }

        public int getResidenceCategory(ClientData clientData, int resi_id)
        {
            // issue a command to read a residence data
            SqlCommand cmd = con.CreateCommand();
            cmd.CommandText = "select r.category from residence r where " +
                "r.resi_id = " + resi_id;
            con.Open();
            int auxCat = (int)cmd.ExecuteScalar();
            con.Close();
            return auxCat;
        }

        public DataSet updateResidence(ClientData clientData, Residence res)
        {
            // dataset to be returned back to the user
            DataSet versionDS = new DataSet();
            // issue a new transaction to update the value of the write-set
            con.Open();
            SqlTransaction myTransaction = con.BeginTransaction();
            SqlCommand myCommand = con.CreateCommand();
            myCommand.Transaction = myTransaction;

            try
            {
                // get the residence and technician category to see if can update
                myCommand.CommandText = "select r.category from residence r where " +
                    "r.resi_id = " + res.Resi_id;
                int resiCat = (int)myCommand.ExecuteScalar();

                // if conditions apply update the data
                // test if it is a trainee and the data is approved
                if (res.Category == 2 || (res.Category == 1 && resiCat < 2) || 
                    (res.Category == 0 && res.Approved == 1 && resiCat < 2))
                {
                    myCommand.CommandText = "update residence set topology='" + res.Topology +
                        "', cooling=" + res.Cooling + ", heating=" +
                        res.Heating + ", isolation=" + res.Isolation +
                        ", coverage='" + res.Coverage + "', glasses='" + res.Glasses + "', " +
                        "category=" + res.Category + " where resi_id=" + res.Resi_id;
                    myCommand.ExecuteNonQuery();

                    myCommand.CommandText = "select * from version_vector where resi_id=" + res.Resi_id;
                    SqlDataReader dataReader = myCommand.ExecuteReader();
                    dataReader.Read();

                    myCommand.CommandText = "update version_vector set server" + database + "=" +
                        (dataReader.GetInt32(database) + 1) + " where resi_id=" + dataReader.GetInt32(0);
                    // close the reader so it doesnt conflict
                    dataReader.Close();
                    myCommand.ExecuteNonQuery();

                    SqlDataAdapter da;
                    SqlCommandBuilder cb;

                    myCommand.CommandText = "select * from version_vector where resi_id=" + res.Resi_id;

                    // get the new version vector
                    da = new SqlDataAdapter(myCommand);
                    cb = new SqlCommandBuilder(da);
                    da.Fill(versionDS, "version_vector");

                    myTransaction.Commit();
                    con.Close();
                    return versionDS;
                }
                else
                {
                    myTransaction.Rollback();
                    con.Close();
                    return versionDS;
                }
            }
            catch (Exception ex)
            {
                myTransaction.Rollback();
                con.Close();
                return null;
            }
        }

    }
}
